﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using hcom;
using System.Data;
using System.Collections;
using System.IO;
using System.Text;
using System.Text.RegularExpressions;
using System.Web.Security;
using System.Security.Principal;

namespace hcms.Admin
{
	public class Model
	{
		#region Construct

		public hData da = new hData();			// 数据库类
		public Hashtable templatesName = new Hashtable();	// 模板类型

		public Model()
		{
			templatesName.Add("t", "栏目模板");
			templatesName.Add("a", "文档模板");
			templatesName.Add("d", "板块");
		}

		#endregion

		/// <summary>根据类型取模板表</summary>
		/// <param name="type">类型名称，如板块，文档模板等</param>
		/// <returns>行集合</returns>
		public DataRowCollection getTemplateRows(string type)
		{
			DataTable dt = da.GetTable(string.Format("select * from hcms_Templates where temDeleted = 0 and temType='{0}'", type));
			return dt.Rows;
		}

		/// <summary>取重定向表</summary>
		/// <returns>行集合</returns>
		public DataRowCollection getRedirectRows()
		{
			DataTable dt = da.GetTable("select * from hcms_Redirect order by redNewUrl");
			return dt.Rows;
		}

		/// <summary>根据指定ID取模板</summary>
		/// <param name="id">指定id</param>
		/// <returns>行集合，只有一行</returns>
		public DataRowCollection getTemplateByID( string id )
		{
			DataTable dt = da.GetTable("select * from hcms_Templates where temID = " + id);
			return dt.Rows;
		}

		/// <summary>取指定ID的下级栏目表	//## 栏目编号是字母c加数字格式，每三位数字为一个级别，最多50个字符。</summary>
		/// <param name="cid">指定栏目ID, id为空时取全部</param>
		/// <param name="subcol">为真时包括所有子栏目</param>
		/// <returns>行集合</returns>
		public DataRowCollection getColumnRows(string cid, bool subcol)
		{
			string strsql = string.Format(
	"SELECT a.*, b.temName AS tname, c.temName AS aname " +
	" FROM hcms_Columns AS a LEFT OUTER JOIN " +
		"hcms_Templates as b ON a.colTemplateID = b.temID LEFT OUTER JOIN " +	// 根据栏目模板ID取栏目模板名称
		"hcms_Templates as c ON a.colArtTemplateID = c.temID " +		// 根据文档模板ID取文档模板名称
	"WHERE (a.colID like '{0}___{1}') and (a.colDeleted = 0) " +
	"ORDER BY {2}colID",
	cid,
	subcol ? "%" : "",
	subcol ? "" : "colLevel desc, "	// 目录树按level排降序，同级目录按cid排序。
	);
			DataTable dt = da.GetTable(strsql);
			return dt.Rows;
		}

		/// <summary>取指定ID的下级栏目表	//## 栏目编号是字母c加数字格式，每三位数字为一个级别，最多50个字符。</summary>
		/// <param name="cid">指定栏目ID, id为空时取全部</param>
		/// <param name="subcol">为真时包括所有子栏目</param>
		/// <param name="page">页号，从1开始, 返回正确页号</param>
		/// <param name="pagerows">每页行</param>
		/// <param name="pages">返回页数</param>
		/// <returns>行集合</returns>
		public DataRowCollection getColumnRows(string cid, bool subcol, ref int page, int pagerows, ref int pages)
		{
			string strsql = string.Format("SELECT Count(*) FROM hcms_Columns " +
					"WHERE (colID like '{0}___{1}') and (colDeleted = 0)",
				cid,
				subcol ? "%" : ""
				);
			DataTable dt = da.GetTable(strsql);
			pages = ((int)dt.Rows[0][0] + pagerows - 1) / pagerows;

			if (page > pages) page = pages;
			else if (page <= 0) page = 1;

			strsql = string.Format(
				"SELECT * FROM " +
					"(SELECT a.*, b.temName AS tname, c.temName AS aname, ROW_NUMBER() OVER( ORDER BY {2}colID ) AS n " +
					" FROM hcms_Columns AS a LEFT OUTER JOIN " +
						"hcms_Templates as b ON a.colTemplateID = b.temID LEFT OUTER JOIN " +	// 根据栏目模板ID取栏目模板名称
						"hcms_Templates as c ON a.colArtTemplateID = c.temID " +					// 根据文档模板ID取文档模板名称
					"WHERE (a.colID like '{0}___{1}') and (a.colDeleted = 0)) AS d " +
				"WHERE n > {3} AND n <= {4}",	// 分页
				cid,
				subcol ? "%" : "",
				subcol ? "" : "colLevel DESC, ",	// 目录树按level排降序，同级目录按cid排序。
				(page - 1) * pagerows,
				page * pagerows
			);
			dt = da.GetTable(strsql);
			return dt.Rows;
		}

		/// <summary>取指定文档的评论列表</summary>
		/// <param name="aid">指定文档ID, 不能为空</param>
		/// <param name="page">【输入输出】页号，从1开始, 返回正确页号</param>
		/// <param name="pagerows">每页行数</param>
		/// <param name="pages">【输出】，返回页数</param>
		/// <returns>行集合</returns>
		public DataRowCollection getCommentsByAid(string aid, ref int page, int pagerows, ref int pages, string filter = "")
		{
			// 取文章ID对应的栏目ID
			// 如果同一个文章对应多个栏目，取第一个栏目。
			string sqlcolumn = string.Format( "SELECT TOP 1 acidColumnID AS acidColID, acidArticleID " +
				"FROM hcms_ArticleIDandColumnID " +
				"WHERE (acidArticleID = N'{0}') AND (acidDeleted = 0)",		// 指定的文档
				aid);

			// 筛选
			string fit = ((filter == null) || (filter.Trim() == ""))
				? ""
				: string.Format(" AND (hcms_Comments.comComment LIKE '%{0}%')", filter.Trim().Replace("'", "''"));	// 允许输入单引号

			// 取总项数
			string strsql = string.Format("SELECT Count(*) " +
				"FROM ({0}) AS b INNER JOIN " +
					"hcms_Articles ON b.acidArticleID = hcms_Articles.artID INNER JOIN " +
					"hcms_Comments ON hcms_Articles.artID = hcms_Comments.comArticleID " +
				"WHERE (hcms_Comments.comDeleted = 0) AND (hcms_Articles.artDeleted = 0)" +	 // 评论未删除，文档未删除
					"{1}",	// 筛选
				sqlcolumn,
				fit
				);

			DataTable dt = da.GetTable(strsql);
			pages = ((int)dt.Rows[0][0] + pagerows - 1) / pagerows;	// 页数

			if (page > pages) page = pages;
			else if (page <= 0) page = 1;

			strsql = string.Format(
				"SELECT * FROM " +
					"(SELECT acidColID, comID, hcms_Articles.artTitle, hcms_Comments.comDate, hcms_Comments.comComment, " +
						"hcms_Comments.comLevel, hcms_Comments.comIP, hcms_Users.userName, hcms_Comments.comArticleID, " +
						"ROW_NUMBER() OVER( ORDER BY hcms_Comments.comLevel DESC, hcms_Comments.comDate DESC ) AS n " +	// 行号
					"FROM ({0}) AS b INNER JOIN " +
						"hcms_Articles ON b.acidArticleID = hcms_Articles.artID INNER JOIN " +
						"hcms_Comments ON hcms_Articles.artID = hcms_Comments.comArticleID LEFT OUTER JOIN " +
						"hcms_Users ON hcms_Comments.comUserID = hcms_Users.userID " +
					"WHERE (hcms_Comments.comDeleted = 0) AND (hcms_Articles.artDeleted = 0){3}) AS a " + // 评论未删除，文档未删除
				"WHERE (n > {1}) AND (n <= {2})",	// 分页
				sqlcolumn,
				(page - 1) * pagerows,
				page * pagerows,
				fit
				);

			dt = da.GetTable(strsql);
			return dt.Rows;
		}

		/// <summary>取指定栏目ID的评论列表，包括子栏目中的文档评论</summary>
		/// <param name="cid">指定栏目ID, cid为空时取全部评论</param>
		/// <param name="page">【输入输出】页号，从1开始, 返回正确页号</param>
		/// <param name="pagerows">每页行数</param>
		/// <param name="pages">【输出】，返回页数</param>
		/// <returns>行集合</returns>
		public DataRowCollection getCommentsByCid(string cid, ref int page, int pagerows, ref int pages, string filter = "")
		{
			// 取栏目ID包括子栏目对应的文章ID，先筛选所有的栏目ID（WHERE acidColumnID LIKE N'{0}%'），
			// 如果同一个文章对应多个栏目，取第一个栏目（MIN(acidColumnID) AS acidColID）。
			string sqlcolumn = string.Format( "SELECT MIN(acidColumnID) AS acidColID, acidArticleID " +
				"FROM hcms_ArticleIDandColumnID " +
				"WHERE (acidColumnID LIKE N'{0}%') AND (acidDeleted = 0) " +		// 包括子栏目
				"GROUP BY acidArticleID",
				cid);

			// 筛选
			string fit = ((filter == null) || (filter.Trim() == ""))
				? ""
				: string.Format(" AND (hcms_Comments.comComment LIKE '%{0}%')", filter.Trim().Replace("'", "''"));	// 允许输入单引号

			// 取总项数
			string strsql = string.Format("SELECT Count(*) " +
				"FROM ({0}) AS b INNER JOIN " +
					"hcms_Articles ON b.acidArticleID = hcms_Articles.artID INNER JOIN " +
					"hcms_Comments ON hcms_Articles.artID = hcms_Comments.comArticleID " +
				"WHERE (hcms_Comments.comDeleted = 0) AND (hcms_Articles.artDeleted = 0)" +	// 未删除
					"{1}",	// 筛选
				sqlcolumn,
				fit
				);

			DataTable dt = da.GetTable(strsql);
			pages = ((int)dt.Rows[0][0] + pagerows - 1) / pagerows;	// 页数

			if (page > pages) page = pages;
			else if (page <= 0) page = 1;

			strsql = string.Format(
				"SELECT * FROM " +
					"(SELECT acidColID, comID, hcms_Articles.artTitle, hcms_Comments.comDate, hcms_Comments.comComment, " +
						"hcms_Comments.comLevel, hcms_Comments.comIP, hcms_Users.userName, hcms_Comments.comArticleID, " +
						"ROW_NUMBER() OVER( ORDER BY hcms_Comments.comLevel DESC, hcms_Comments.comDate DESC ) AS n " +	// 行号
					"FROM ({0}) AS b INNER JOIN " +
						"hcms_Articles ON b.acidArticleID = hcms_Articles.artID INNER JOIN " +
						"hcms_Comments ON hcms_Articles.artID = hcms_Comments.comArticleID LEFT OUTER JOIN " +
						"hcms_Users ON hcms_Comments.comUserID = hcms_Users.userID " +
					"WHERE (hcms_Comments.comDeleted = 0) AND (hcms_Articles.artDeleted = 0){3}) AS a " + // 评论未删除，栏目未删除
				"WHERE (n > {1}) AND (n <= {2})",	// 分页
				sqlcolumn,
				(page - 1) * pagerows,
				page * pagerows,
				fit
				);

			dt = da.GetTable(strsql);
			return dt.Rows;
		}

		/// <summary>
		/// 取指定评论id的评论
		/// </summary>
		/// <param name="id">评论id</param>
		/// <returns>评论行</returns>
		public DataRow getCommentByID(string id)
		{
			string strsql = string.Format( "SELECT * FROM hcms_Comments WHERE comID='{0}'", id);
			DataTable dt = da.GetTable(strsql);
			return dt.Rows[0];
		}

		/// <summary>取当前id及所有父节点</summary>
		/// <param name="cid">指定cid</param>
		/// <returns>当前及所有父节点行集合</returns>
		public DataRowCollection getCurrentPos(string cid)
		{
			string strsql = string.Format("SELECT colID, colName FROM hcms_Columns WHERE ('{0}' LIKE colID + '%') ORDER BY colID", cid);
			DataTable dt = da.GetTable(strsql);
			return dt.Rows;
		}

		/// <summary>取指定ID的栏目。</summary>
		/// <param name="cid">指定栏目ID</param>
		/// <returns>选择的栏目</returns>
		public DataRow getColumnByID(string cid)
		{
			string strsql = string.Format("select * from hcms_Columns where colID = '{0}'",
				cid
				);
			DataTable dt = da.GetTable(strsql);
			return dt.Rows[0];
		}

		/// <summary>取指定标识的栏目，栏目可以有多种标识，用逗号隔开。</summary>
		/// <param name="tag">指定的标识</param>
		/// <returns>行集合</returns>
		public DataRowCollection getColumnsByTag(string tag)
		{
			string strsql = string.Format("SELECT colID, colName FROM hcms_Columns WHERE (colTag LIKE '%{0}%') AND (colDeleted = 0) ORDER BY colLevel DESC, colID", tag);
			DataTable dt = da.GetTable(strsql);
			return dt.Rows;
		}

		/// <summary>获取下一个新建栏目id，最大子栏目号加1</summary>
		/// <param name="pid">父栏目id</param>
		/// <returns>新栏目id</returns>
		public string getnewtid( string pid )
		{
			string strsql = string.Format("SELECT max(colID) FROM hcms_Columns WHERE (colID LIKE '{0}___')", pid);
			DataTable dt = da.GetTable(strsql);
			if (dt.Rows[0][0] == DBNull.Value)
	return pid + "001";

			string s = (string)dt.Rows[0][0];
			s = s.Substring(s.Length - 3);
			int n = int.Parse(s) + 1;			//## 最多999个子栏目
			return pid + n.ToString("000");
		}

		/// <summary>取指定栏目ID的文章表，不分页</summary>
		/// <param name="cid">指定栏目ID</param>
		/// <param name="subcol">为真时包括所有子栏目文章</param>
		/// <returns>行集合, 按级别和发布日期降序排列</returns>
		public DataRowCollection getArticleRows(string cid, bool subcol)
		{
			string strsql = string.Format("SELECT a.*, b.acidColumnID " +
				"FROM hcms_Articles AS a INNER JOIN " +
					"(SELECT acidArticleID, acidColumnID " +
					"FROM hcms_ArticleIDandColumnID " +
					"WHERE (acidColumnID LIKE '{0}{1}') AND (acidDeleted = 0)) AS b ON a.artID = b.acidArticleID " +
				"ORDER BY a.artLevel DESC, a.artUpdateTime DESC",
				cid,
				subcol ? "%" : ""
				);
			DataTable dt = da.GetTable(strsql);
			return dt.Rows;
		}

		/// <summary>取指定栏目ID的文章表，分页</summary>
		/// <param name="cid">指定栏目ID</param>
		/// <param name="subcol">为真时包括所有子栏目文章</param>
		/// <param name="page">页号，从1开始, 返回正确页号</param>
		/// <param name="pagerows">每页行</param>
		/// <param name="pages">返回页数</param>
		/// <returns>行集合, 按级别和发布日期降序排列</returns>
		public DataRowCollection getArticleRows(string cid, bool subcol, ref int page, int pagerows, ref int pages)
		{
			string strsql = string.Format("SELECT Count(*) FROM hcms_ArticleIDandColumnID " +
					"WHERE (acidColumnID LIKE '{0}{1}') AND (acidDeleted = 0)",
				cid,
				subcol ? "%" : ""
				);
			DataTable dt = da.GetTable(strsql);
			pages = ((int)dt.Rows[0][0] + pagerows - 1) / pagerows;

			if (page > pages) page = pages;
			else if (page <= 0) page = 1;

			strsql = string.Format(
				"SELECT * FROM " +
					"(SELECT a.*, b.acidColumnID, ROW_NUMBER() OVER (ORDER BY a.artLevel DESC, a.artUpdateTime DESC) as n " +
					"FROM hcms_Articles AS a INNER JOIN " +
						"(SELECT acidArticleID, acidColumnID " +
						"FROM hcms_ArticleIDandColumnID " +
						"WHERE (acidColumnID LIKE '{0}{1}') AND (acidDeleted = 0)) AS b ON a.artID = b.acidArticleID) AS c " +
				"WHERE n > {2} AND n <= {3}",	// 分页
				cid,
				subcol ? "%" : "",
				(page - 1) * pagerows,
				page * pagerows
			);
			dt = da.GetTable(strsql);
			return dt.Rows;
		}

		/// <summary>
		/// 按指定id取用户
		/// </summary>
		/// <param name="userid">用户id</param>
		/// <returns></returns>
		public DataRow getUserByID( string userid )
		{
			string strsql = string.Format("SELECT * FROM hcms_Users WHERE userID = '{0}'", userid);
			DataTable dt = da.GetTable(strsql);
			return dt.Rows[0];
		}

		/// <summary>取用户表，分页</summary>
		/// <param name="page">页号，从1开始, 返回正确页号</param>
		/// <param name="pagerows">每页行</param>
		/// <param name="pages">返回页数</param>
		/// <param name="filter">查找字符串，查找所有字段，横杆起头表示从字段开始查找，横杆结束表示查到字段尾部</param>
		/// <returns>行集合, 按注册日期降序排列</returns>
		public DataRowCollection getUserRows(ref int page, int pagerows, ref int pages, string filter)
		{
			// 条件
			string fit = ((filter == null) || (filter.Trim().Length == 0))
				? ""
				: string.Format(" AND ('-' + (convert(varchar(50),userDate,120) + '-' +" +
					"userName + '-' + userMail + '-' + userRoles + '-' + userIP + '-') LIKE '%{0}%')"
					, filter.Trim().Replace("'", "''"));
			
			string strsql = string.Format("SELECT Count(*) FROM hcms_Users " +
				"WHERE (userDeleted = 0){0}",
				fit	// 加条件
			);
			DataTable dt = da.GetTable(strsql);
			pages = ((int)dt.Rows[0][0] + pagerows - 1) / pagerows;

			if (page > pages) page = pages;
			else if (page <= 0) page = 1;

			strsql = string.Format(
				"SELECT * FROM " +
					"(SELECT *, ROW_NUMBER() OVER (ORDER BY userDate DESC) as n " +
					"FROM hcms_Users WHERE (userDeleted = 0){0}) as a " +
				"WHERE n > {1} AND n <= {2}",	// 分页
				fit,	// 加条件
				(page - 1) * pagerows,
				page * pagerows
			);
			dt = da.GetTable(strsql);
			return dt.Rows;
		}

		/// <summary>按指定的aid取文章</summary>
		/// <param name="aid">指定的文章id</param>
		/// <returns>文章记录行</returns>
		public DataRow getArticleByID(string aid)
		{
			string strsql = string.Format("SELECT a.*, b.userName FROM " +
				"(SELECT * FROM hcms_Articles  WHERE artID = '{0}') as a " +
				"LEFT JOIN hcms_Users as b ON a.artUserID = b.userID", aid);
			DataTable dt = da.GetTable(strsql);
			if (dt.Rows.Count > 0)
				return dt.Rows[0];
			else
				throw (new Exception("文档不存在。"));
		}

		/// <summary>根据aid取栏目集合</summary>
		/// <param name="aid">文档ID</param>
		/// <returns>对应的栏目集合</returns>
		public DataRowCollection getColumnsByAid(string aid)
		{
			string strsql = string.Format("SELECT * FROM hcms_Columns WHERE colID IN " +
				"(SELECT acidColumnID FROM hcms_ArticleIDandColumnID WHERE (acidArticleID = '{0}' AND acidDeleted = 0))",
				aid
				);
			DataTable dt = da.GetTable(strsql);
			return dt.Rows;
		}

		/// <summary>取多栏目文档id集合，不含子栏目。</summary>
		/// <param name="cids">指定栏目ID集, 格式 "cid1,cid2,cid3..."</param>
		/// <param name="subcol">为真时包括所有子栏目文档，否则只取当前栏目下的文档</param>
		/// <returns>行集合</returns>
		public DataRowCollection aidsByCid(string cids, bool subcol)
		{
			//DataRowCollection rows = null;
			DataTable dts = new DataTable();
			string[] arr = cids.Split(',');
			int n = arr.Length;
			for (int i = 0; i < n; i++)
			{
				string strsql = subcol
					? string.Format(
						"SELECT * FROM hcms_ArticleIDandColumnID WHERE (acidColumnID LIKE '{0}%') AND (acidDeleted = 0)",	// 包括子栏目
						arr[i]
						)
					: string.Format(
						"SELECT * FROM hcms_ArticleIDandColumnID WHERE (acidColumnID = '{0}') AND (acidDeleted = 0)",	// 不含子栏目
						arr[i]
						);

	DataTable dt = da.GetTable(strsql);
	dts.Merge( dt );
	/*dt.NewRow()
	DataRowCollection rs = da.GetTable(strsql).Rows;

	if (rows == null)
		rows = rs;
	else
	{
		int m = rs.Count;
		for (int j = 0; j < n; j++)
			
			rows.Add(rs[j]);
	}*/
			}
			return dts.Rows;
		}

		/// <summary>更新 AID CID 对应表, 同时更新文档模板（usedtid）</summary>
		/// <param name="aid">文档ID</param>
		/// <param name="cids">栏目ID集，格式 ID1,ID2,ID3...</param>
		public void saveArticleCids(string aid, string cids)
		{
			// 先删除所有栏目ID, 因为保存文档时栏目id可能会变化
			string strsql = string.Format("UPDATE hcms_ArticleIDandColumnID SET acidDeleted = 1 WHERE acidArticleID = '{0}'", aid);
			da.Execute(strsql);

			// 建立栏目ID
			string[] arcid = cids.Split(',');
			int n = arcid.Length;
			for (int i = 0; i < n; i++)
			{
				// 先恢复
				strsql = string.Format("UPDATE hcms_ArticleIDandColumnID SET acidDeleted = 0 WHERE acidArticleID = '{0}' AND acidColumnID = '{1}'", aid, arcid[i]);
				int b = da.Execute(strsql);

				if (b == 0)	// 没有则添加
				{
					strsql = string.Format("INSERT INTO hcms_ArticleIDandColumnID( acidArticleID, acidColumnID ) VALUES( '{0}', '{1}' )", aid, arcid[i]);
					da.Execute(strsql);
				}
			}
		}

		/// <summary>删除指定aids cid的文档, ** 同时删除生成的静态文档。</summary>
		/// <param name="aids">文档id, 格式 aid1,aid2,aid3...</param>
		/// <param name="cid">栏目id</param>
		/// <returns>返回删除的行数</returns>
		public int delArticles(string aids, string cid)
		{
			// 删除静态页面
			delFiles(aids);

			// 删除数据库
			string strsql = string.Format("UPDATE hcms_ArticleIDandColumnID SET acidDeleted = 1 WHERE ((acidColumnID = '{0}') AND (acidArticleID IN ('{1}')))",
				cid, aids.Replace(",", "','"));	// 把id加上引号变成字符串形式
			return da.Execute(strsql);
		}

		/// <summary>删除多个静态文档页面， ?? 删除很多文件时，会不会很慢，加个等待标志，或后台操作。</summary>
		/// <param name="ids">文档或栏目id集，格式：id1, id2, id3...</param>
		/// <param name="deltype">为0表示aids, 1表示cids</param>
		public void delFiles(string ids)
		{
			string dir = System.AppDomain.CurrentDomain.BaseDirectory + "Articles\\";
			string[] arr = ids.Split(',');
			int n = arr.Length;
			foreach (string entry in Directory.GetFiles(dir))
			{
				for (int i = 0; i < n; i++)
				{
					// 静态文档文件名entry是全路径加文件名，结构是cid-aid-tid.htm，
					// 例如：E:\Samples\Articles\001-59779bb8-6bf5-4b95-a302-d41f0d1bea75-3.htm
					// 为了保证查找cid号是文件名的开头部分，在cid之前加前导字符"\\"，包括子栏目, tid前后各加限定符，保证唯一。
					if (entry.IndexOf(arr[i]) > 0)
						File.Delete(entry);	// 删除静态页面
				}
			}
		}

		/// <summary>根据文档模板集合删除多个静态文档页面， ?? 删除很多文件时，会不会很慢，加个等待标志，或后台操作。</summary>
		/// <param name="tids">文档模板集合，格式tid1,tid2,tid3....</param>
		public void delFilesTid(string tids)
		{
			string strsql = string.Format("SELECT * FROM hcms_ArticleIDandColumnID WHERE acidTemplateID in ({0})", tids);
			DataTable dt = da.GetTable(strsql);
			StringBuilder str = new StringBuilder(10000);
			foreach (DataRow row in dt.Rows)
			{
				str.AppendFormat("\\{0}-{1},", row["acidColumnID"], row["acidArticleID"]);
			}
			if (str.Length > 0)
			{
				str.Length--;	// 去掉最后的逗号
				delFiles(str.ToString());

				// 把hcms_ArticleIDandColumnID中对应的模板项设为空
				strsql = string.Format("UPDATE hcms_ArticleIDandColumnID SET acidTemplateID = null WHERE acidTemplateID in ({0})", tids);
				da.Execute(strsql);
			}
		}

		/// <summary>删除一个静态页面，异常时写入日志，不中断程序，保证删除多个文件的连续性</summary>
		/// <param name="aid">指定的文档ID</param>
		/// <returns>true: success, false: failed</returns>
		/*public bool delHtmfile(string aid)
		{
			try
			{
	File.Delete(System.AppDomain.CurrentDomain.BaseDirectory + "Articles\\a" + aid + ".htm");	// 删除静态页面
	return true;
			}
			catch (Exception ex)
			{
	new hcom.hLog().Write("删除文件失败。", ex);
	return false;
			}
		}*/


		/// <summary>获取系统参数</summary>
		/// <param name="oname">参数名</param>
		/// <returns>返回对应的参数</returns>
		public string getOption(string oname)
		{
			string sqlstr = string.Format("SELECT oValue FROM hcms_Options WHERE oName = '{0}'", oname);
			DataTable dt = da.GetTable(sqlstr);
			if (dt.Rows.Count == 0)
			{
	throw (new Exception("取系统参数失败，无效参数名('" + oname + "')"));
			}

			return dt.Rows[0][0].ToString();
		}

		/// <summary>设置系统参数</summary>
		/// <param name="oname">参数名</param>
		/// <param name="ovalue">参数值</param>
		public void setOption(string oname, string ovalue)
		{
			string sqlstr = string.Format("UPDATE hcms_Options SET oValue = '{0}'  WHERE oName = '{1}'", ovalue, oname);
			if( da.Execute( sqlstr ) == 0 )
			{
	throw (new Exception("存系统参数失败，无效参数名('" + oname + "')"));
			}
		}

		/// <summary>取文档模板，首先从文档库中查文档模板，如果没有，从栏目及所有父栏目中查文档模板，
		/// 如果没有，取系统默认的文档模板，如果没有，直接取第一个文档模板，如果没有，抛出
		/// 异常，显示至少创建一个文档模板。
		/// </summary>
		/// <param name="aid">文档ID</param>
		/// <param name="cid">栏目ID，进入文档的栏目，文档可能有多个栏目。</param>
		/// <returns>返回文档模板id, 没有产生异常</returns>
		public int getArtTemplate(string aid, string cid)
		{
			// 从文档中取文档模板
			string sqlstr;
			DataTable dt;
			if (aid.Length > 0)		// 新建文档时，aid == ""
			{
				sqlstr = string.Format("SELECT a.artTemplateID " +
					"FROM hcms_Articles AS a INNER JOIN hcms_Templates AS b ON a.artTemplateID = b.temID " +
					"WHERE ((a.artID = '{0}') and (b.temDeleted = 0))",
					aid
					);
				dt = da.GetTable(sqlstr);
				if (dt.Rows.Count > 0)
				{
					return (int)dt.Rows[0][0];	// 如果文档中指定模板，并且模板有效，返回模板ID
				}
			}

			// 从栏目中取文档模板
			sqlstr = string.Format("SELECT a.colArtTemplateID " +
				"FROM hcms_Columns AS a INNER JOIN hcms_Templates AS b ON a.colArtTemplateID = b.temID " +
				"WHERE (('{0}' LIKE a.colID + '%') and (b.temDeleted = 0)) " +
				"ORDER BY a.colID DESC", cid);
			dt = da.GetTable(sqlstr);
			if (dt.Rows.Count > 0)
			{
				return (int)dt.Rows[0][0];	// 如果文档中没有指定文档模板，从栏目中查找文档模板。
			}

			// 取系统默认的文档模板
			sqlstr = "SELECT oValue " +
				"FROM (SELECT * FROM hcms_Options WHERE oName = '默认文档模板') AS a " +
				"INNER JOIN hcms_Templates AS b ON a.oValue = b.temID " +
				"WHERE (b.temDeleted = 0)";
			dt = da.GetTable(sqlstr);
			if ((dt.Rows.Count > 0) && (dt.Rows[0][0] != DBNull.Value))
			{
				return int.Parse((string)dt.Rows[0][0]);	// 取系统默认的文档模板。
			}

			// 取第一个文档模板
			sqlstr = "SELECT TOP(1) tid FROM hcms_Templates WHERE ((temType = 'a') AND (temDeleted = 0))";
			dt = da.GetTable(sqlstr);
			if (dt.Rows.Count > 0)
			{
	return (int)dt.Rows[0][0];	// 取系统默认的文档模板。
			}

			throw( new Exception( "至少要创建一个文档模板。" ) );
		}

		/// <summary>取栏目模板，从当前栏目及所有父栏目中查栏目模板，
		/// 如果没有，取系统默认的栏目模板，如果没有，直接取第一个栏目模板，如果没有，抛出
		/// 异常，显示至少创建一个文档模板。
		/// </summary>
		/// <param name="cid">栏目ID</param>
		/// <returns>返回栏目模板id, 没有产生异常</returns>
		public int getColTemplate(string cid)
		{
			// 从栏目中取栏目模板，先取子栏目的栏目模板
			string sqlstr = string.Format("SELECT a.colTemplateID " +
				"FROM hcms_Columns AS a INNER JOIN hcms_Templates AS b ON a.colTemplateID = b.temID " +
				"WHERE (('{0}' LIKE a.colID + '%') and (b.temDeleted = 0)) " +
				"ORDER BY a.colID DESC", cid);
			DataTable dt = da.GetTable(sqlstr);
			if (dt.Rows.Count > 0)
			{
				return (int)dt.Rows[0][0];	// 从栏目中查找文档模板。
			}

			// 取系统默认的栏目模板
			sqlstr = "SELECT oValue " +
				"FROM (SELECT * FROM hcms_Options WHERE oName = '默认栏目模板') AS a " +
				"INNER JOIN hcms_Templates AS b ON a.oValue = b.temID " +
				"WHERE (b.temDeleted = 0)";
			dt = da.GetTable(sqlstr);
			if ((dt.Rows.Count > 0) && (dt.Rows[0][0] != DBNull.Value))
			{
				return int.Parse((string)dt.Rows[0][0]);	// 取系统默认的文档模板。
			}

			// 取第一个栏目模板
			sqlstr = "SELECT TOP(1) tid FROM hcms_Templates WHERE ((temType = 't') AND (temDeleted = 0))";
			dt = da.GetTable(sqlstr);
			if (dt.Rows.Count > 0)
			{
				return (int)dt.Rows[0][0];
			}

			throw (new Exception("至少要创建一个栏目模板。"));
		}

		/// <summary>暂时未用，取文档页面标题，首先从文档库中查页面标题，如果没有，从栏目及所有父栏目中查找页面标题，
		/// 如果没有，不设置，使用模板标题。
		/// </summary>
		/// <param name="aid">文档ID, 新建文档和取栏目页面标题时，aid=""</param>
		/// <param name="cid">栏目ID，进入文档的栏目，文档可能有多个栏目。</param>
		/// <returns></returns>
		public string getPageTitle(string aid, string cid)
		{
			// 从文档中取
			string sqlstr;
			DataTable dt;
			if (aid.Length > 0)		// 新建文档时，aid == ""
			{
				sqlstr = string.Format("SELECT artBrowserTitle " +
					"FROM hcms_Articles " +
					"WHERE ((artID = '{0}') and (artBrowserTitle != ''))",
					aid
					);
				dt = da.GetTable(sqlstr);
				if (dt.Rows.Count > 0)
				{
					return (string)dt.Rows[0][0];	// 如果文档中指定页面标题，返回标题
				}
			}

			// 从栏目及父栏目中取文档标题
			sqlstr = string.Format("SELECT colBroswerTitle " +
				"FROM hcms_Columns " +
				"WHERE (('{0}' LIKE colID + '%') and (colDeleted = 0) and (colBroswerTitle != ''))", cid);
			dt = da.GetTable(sqlstr);
			if (dt.Rows.Count > 0)
			{
				return (string)dt.Rows[0][0];	// 如果文档中没有指定文档模板，从栏目中查找文档模板。
			}

			return "驾驶技术网";
		}

		/// <summary>取参考文档，先取文档定义的参考文档，再从对应栏目中获取</summary>
		/// <param name="aid">文档id</param>
		/// <param name="cid">文档栏目</param>
		/// <param name="num">条数</param>
		/// <returns>哈希表，key:cid-aid，value:title</returns>
		public Hashtable getAssociate(string aid, string cid, int num)
		{
			if (aid == null || aid.Length == 0)
				return null;


			/* 文档很多时用这种方法效率可能很低
			string strsql = string.Format(
				"(SELECT a.title, b.cid " +
				"FROM hcms_Articles AS a INNER JOIN " +
					"(SELECT cid " +
					"FROM hcms_ArticleIDandColumnID " +
					"WHERE ((cid + '-' + aid) in ('{0}')) AND (deleted = 0)) AS b ON a.aid = b.aid) AS c ",
				associate.Replace("\n", "','")
			);
			DataTable dt = da.GetTable(strsql);*/

			// 取文档定义的参考文档
			string strsql = string.Format("SELECT artAssociate FROM hcms_Articles WHERE artID='{0}'", aid);
			string str = da.ExecuteScalar(strsql).ToString();
			//"004-59779bb8-6bf5-4b95-a302-d41f0d1bea75-18.htm"
			Regex r = new Regex(@"(\d+)-(\w{8}-\w{4}-\w{4}-\w{4}-\w{12})");
			MatchCollection matchCollection = r.Matches(str);
			int n = matchCollection.Count;
			int count = 1;
			Hashtable hashAssociate = new Hashtable();
			for (int i = 0; i < n && count <= num; i++)
			{
				Match m = matchCollection[i];
				strsql = string.Format(
					"SELECT a.artTitle, b.acidColumnID, b.acidArticleID " +
					"FROM hcms_Articles AS a INNER JOIN " +
						"(SELECT acidArticleID, acidColumnID " +
						"FROM hcms_ArticleIDandColumnID " +
						"WHERE (acidColumnID='{0}') AND (acidArticleID='{1}') AND (acidDeleted = 0)) AS b ON a.artID = b.acidArticleID",
					m.Result("$1"),
					m.Result("$2")
				);
				DataRowCollection rows = da.GetTable(strsql).Rows;
				if (rows.Count > 0)
				{
					DataRow row = rows[0];
					string key = row["acidColumnID"] + "-" + row["acidArticleID"];
					if (hashAssociate[key] == null)
					{
						hashAssociate.Add(key, row["artTitle"]);
						count++;
					}
				}
			}
			if (count > num)
				return hashAssociate;

			// 取同栏目的文档
			DataRowCollection rows2 = getArticleRows(cid, false);
			n = rows2.Count;
			for (int i = 0; i < n && count <= num; i++)
			{
				DataRow row = rows2[i];
				string key = row["acidColumnID"] + "-" + row["artID"];
				if (hashAssociate[key] == null)
				{
					hashAssociate.Add(key, row["artTitle"]);
					count++;
				}
			}
			return hashAssociate;
		}


		#region 登录及安全控制

		/// <summary>
		/// 登录
		/// </summary>
		/// <param name="username">用户名或email</param>
		/// <param name="password">密码，当password为null时，不判断密码</param>
		/// <param name="autologin">为true时永久有效</param>
		/// <returns>成功OK, 否则Error</returns>
		public string Login(string username, string password, bool autologin)
		{
			if ((username == null) || (username.Length == 0))
				return "用户名或密码错误";

				DataRow row = null;
				string str = "";
				string sqlstr = string.Format("SELECT * FROM [hcms_Users] WHERE ((userMail='{0}') or (userName='{0}'){1})",
					username.Replace("'", "''"),	//?? 没有防注入
					password == null ? "" : string.Format(" and (userPassword='{0}')", password.Replace("'", "''"))
					);
				DataTable dt = da.GetTable(sqlstr);
				if (dt.Rows.Count == 0)
					return "用户名或密码错误";

				row = dt.Rows[0];
				str = string.Format("<UserID>{0}</UserID>" +
					"<Username>{1}</Username>" +
					"<eMail>{2}</eMail>" +
					"<Roles>{3}</Roles>",
					row["userID"],
					row["userName"],
					row["userMail"],
					row["userRoles"]
					);

				FormsAuthenticationTicket Ticket = new FormsAuthenticationTicket(
					1,
					row["userID"].ToString(),
					DateTime.Now,
					DateTime.Now.AddYears(50),	//使用过程中永不过期，但Cookie.Expires未设的话，退出浏览器时Cookie就被删除了。
					true,
					str, //调用UserToRole方法来获取role字符串
					"/"); //建立身份验证票对象

				string HashTicket = FormsAuthentication.Encrypt(Ticket); //加密序列化验证票为字符串
				HttpCookie UserCookie = new HttpCookie(FormsAuthentication.FormsCookieName, HashTicket);
				if (autologin)	//ie11 不启作用，用工具分析Cookie 
					UserCookie.Expires = DateTime.Now.AddYears(50);		//http://blog.csdn.net/byondocean/article/details/7164117  //是否持久性(根据需要设置,若是设置为持久性,在发出cookie时,cookie的Expires设置一定要设置)
				//生成Cookie
				HttpContext.Current.Response.Cookies.Add(UserCookie); //输出Cookie

				// 在Cookie中保存未加密的用户名和级别，便于html文件使用 // 不用客户端另外保存用户信息 20140104
                //str = row["userid"] + "|" + row["userName"] + "|" + row["userRoles"];
                //str = HttpUtility.UrlEncode(str);
                //HttpContext.Current.Response.Cookies.Add(new HttpCookie("hUserInfo", str));
				//if (autologin)
				//	HttpContext.Current.Response.Cookies["hUserInfo"].Expires = DateTime.Now.AddYears(10);

				return (row["userid"] + "|" + row["userName"] + "|" + row["userRoles"]);

		}

		public void Logout()
		{
			FormsAuthentication.SignOut();
            //HttpContext.Current.Response.Cookies.Remove("hUserInfo");	//删除自定义Cookie.  // 不用客户端另外保存用户信息 20140104
			//HttpContext.Current.Response.Cookies["hUserInfo"].ToString(); //?? 不加这一句，上一句不起作用。
		}


		/// <summary>
		/// 加上角色
		/// </summary>
		public void Authenticate()
		{
			if (HttpContext.Current.Request.IsAuthenticated) //验证过的用户才进行role的处理
			{
				FormsIdentity Id = (FormsIdentity)HttpContext.Current.User.Identity;
				FormsAuthenticationTicket Ticket = Id.Ticket; //取得身份验证票
				string[] Roles = getNoteString("Roles").Split(','); //将身份验证票中的role数据转成字符串数组
				HttpContext.Current.User = new GenericPrincipal(Id, Roles); //将原有的Identity加上角色信息新建一个GenericPrincipal表示当前用户,这样当前用户就拥有了role信息
			}
		}

		/// <summary>
		/// 取UserData的某值
		/// </summary>
		/// <param name="key">关键字</param>
		/// <returns>返回相应的值，没有登录或无效关键字返回""</returns>
		public string getNoteString(string key)
		{
			if (!HttpContext.Current.Request.IsAuthenticated) //验证过的用户才进行role的处理
				return "";

			string str = string.Format("<{0}>(.*)</{0}>", key);
			FormsIdentity Id = (FormsIdentity)HttpContext.Current.User.Identity;
			FormsAuthenticationTicket Ticket = Id.Ticket; //取得身份验证票
			Regex r = new Regex(str, RegexOptions.Compiled);
			Match m = r.Match(Ticket.UserData);
			if (!m.Success)
				return "";

			return m.Groups[1].Value;
		}

		#endregion

	}
}